package com.ec.bl.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ec.bl.domain.vo.HealthAlertVO;
import com.ec.bl.domain.vo.PendingTaskVO;
import com.ec.bl.domain.vo.RecentFeedbackVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

@Mapper
public interface DashboardMapper extends BaseMapper<Object> { // BaseMapper的泛型可以为任意实体，这里我们主要用自定义SQL

    // --- Metrics Queries ---
    @Select("SELECT COUNT(*) FROM elderly_profiles WHERE check_in_status = 'in_residence'")
    long countResidents();

    @Select("SELECT COUNT(*) FROM health_records WHERE is_abnormal = 1 AND DATE(recorded_at) = CURDATE()")
    long countHealthAlertsToday();

    @Select("SELECT COUNT(*) FROM beds WHERE status = 'vacant'")
    long countVacantBeds();

    // --- Pending Tasks Queries ---
    @Select({
            "(SELECT cio.id, ep.full_name as applicantName, NULL as relatedElderlyName, cio.created_at as taskTime, ",
            "CASE cio.request_type WHEN 'check_in' THEN '入住申请' ELSE '退住申请' END as taskType ",
            "FROM check_in_out_requests cio JOIN elderly_profiles ep ON cio.elderly_user_id = ep.user_id WHERE cio.status = 'pending') ",
            "UNION ALL ",
            "(SELECT lr.id, ep.full_name as applicantName, NULL as relatedElderlyName, lr.created_at as taskTime, '请假申请' as taskType ",
            "FROM leave_requests lr JOIN elderly_profiles ep ON lr.elderly_user_id = ep.user_id WHERE lr.status = 'pending') ",
            "ORDER BY taskTime DESC LIMIT 10"
    })
    List<PendingTaskVO> getPendingApplications();

    @Select("SELECT va.id, va.visitor_name as applicantName, ep.full_name as relatedElderlyName, va.visit_time as taskTime, '探访预约' as taskType " +
            "FROM visit_appointments va JOIN elderly_profiles ep ON va.elderly_user_id = ep.user_id " +
            "WHERE va.status = 'pending' ORDER BY va.visit_time ASC LIMIT 5")
    List<PendingTaskVO> getPendingAppointments();

    // --- Feedback Query ---
    @Select("SELECT f.id, " +
            "CASE u.user_type WHEN 'family' THEN CONCAT('家属-', fp.full_name) ELSE CONCAT('老人-', ep.full_name) END as user, " +
            "f.content, " +
            "CASE WHEN f.reply IS NULL THEN '待处理' ELSE '已回复' END as status " +
            "FROM feedbacks f " +
            "JOIN users u ON f.user_id = u.id " +
            "LEFT JOIN family_profiles fp ON u.id = fp.user_id AND u.user_type = 'family' " +
            "LEFT JOIN elderly_profiles ep ON u.id = ep.user_id AND u.user_type = 'elderly' " +
            "WHERE f.reply IS NULL " + // 只查询待处理的
            "ORDER BY f.created_at DESC LIMIT 5")
    List<RecentFeedbackVO> getLatestFeedback();

    // --- Health Alerts Query ---
    @Select("SELECT hr.id, ep.full_name as elderlyName, hr.record_type as alertType, " +
            "CONCAT('测量值: ', hr.value1, IF(hr.value2 IS NOT NULL, CONCAT('/', hr.value2), ''), ' ', hr.unit) as description, " +
            "hr.recorded_at as recordTime " +
            "FROM health_records hr JOIN elderly_profiles ep ON hr.elderly_user_id = ep.user_id " +
            "WHERE hr.is_abnormal = 1 ORDER BY hr.recorded_at DESC LIMIT 5")
    List<HealthAlertVO> getRealtimeHealthAlerts();

    // --- Chart Queries ---
    @Select("SELECT COUNT(*) FROM beds")
    long countTotalBeds();

    @Select("SELECT DATE(created_at) as date, COUNT(id) as count " +
            "FROM check_in_out_requests " +
            "WHERE request_type = 'check_in' AND status = 'approved' AND created_at >= #{startDate} " +
            "GROUP BY DATE(created_at)")
    List<Map<String, Object>> getNewCheckInsPerDay(LocalDate startDate);
}
